import sqlite3
import hashlib
from typing import List, Dict, Any, Optional, Tuple
from datetime import datetime
from models import User, Consultation, Points, PointRecord, Admin, UserStats, ConsultationStats

class DatabaseManager:
    def __init__(self, db_path: str = 'car_consultation.db'):
        self.db_path = db_path
        self.init_database()
    
    def get_connection(self) -> sqlite3.Connection:
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn
    
    def init_database(self):
        """初始化数据库表结构"""
        conn = self.get_connection()
        try:
            # 用户表
            conn.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    name TEXT NOT NULL,
                    phone TEXT NOT NULL,
                    budget REAL,
                    preferred_type TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 咨询记录表
            conn.execute('''
                CREATE TABLE IF NOT EXISTS consultations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    budget_range TEXT NOT NULL,
                    preferred_type TEXT NOT NULL,
                    usage_scenario TEXT NOT NULL,
                    fuel_type TEXT NOT NULL,
                    brand_preference TEXT,
                    consultation_content TEXT NOT NULL,
                    llm_response TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            ''')
            
            # 积分表
            conn.execute('''
                CREATE TABLE IF NOT EXISTS points (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    points INTEGER DEFAULT 0,
                    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            ''')
            
            # 积分记录表
            conn.execute('''
                CREATE TABLE IF NOT EXISTS point_records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    points_change INTEGER NOT NULL,
                    reason TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id)
                )
            ''')
            
            # 管理员表
            conn.execute('''
                CREATE TABLE IF NOT EXISTS admins (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT UNIQUE NOT NULL,
                    password TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 插入默认管理员
            admin_password = hashlib.md5("admin123".encode()).hexdigest()
            try:
                conn.execute(
                    'INSERT OR IGNORE INTO admins (username, password) VALUES (?, ?)', 
                    ('admin', admin_password)
                )
            except sqlite3.IntegrityError:
                pass
            
            conn.commit()
            print("数据库初始化完成")
            
        except Exception as e:
            print(f"数据库初始化失败: {e}")
            conn.rollback()
        finally:
            conn.close()
    
    def create_user(self, username: str, password: str, name: str, phone: str, 
                   budget: Optional[float] = None, preferred_type: Optional[str] = None) -> Tuple[bool, Optional[int], str]:
        """创建新用户"""
        conn = self.get_connection()
        try:
            cursor = conn.execute(
                'INSERT INTO users (username, password, name, phone, budget, preferred_type) VALUES (?, ?, ?, ?, ?, ?)',
                (username, password, name, phone, budget, preferred_type)
            )
            user_id = cursor.lastrowid
            
            # 初始化积分
            conn.execute('INSERT INTO points (user_id, points) VALUES (?, ?)', (user_id, 100))
            conn.execute(
                'INSERT INTO point_records (user_id, points_change, reason) VALUES (?, ?, ?)',
                (user_id, 100, '注册奖励')
            )
            
            conn.commit()
            return True, user_id, "注册成功"
            
        except sqlite3.IntegrityError:
            return False, None, "用户名已存在"
        except Exception as e:
            return False, None, f"注册失败: {str(e)}"
        finally:
            conn.close()
    
    def get_user_by_credentials(self, username: str, password: str) -> Optional[User]:
        """根据用户名和密码获取用户"""
        conn = self.get_connection()
        try:
            row = conn.execute(
                'SELECT * FROM users WHERE username = ? AND password = ?', 
                (username, password)
            ).fetchone()
            
            if row:
                return User(
                    user_id=row['id'],
                    username=row['username'],
                    password=row['password'],
                    name=row['name'],
                    phone=row['phone'],
                    budget=row['budget'],
                    preferred_type=row['preferred_type'],
                    created_at=row['created_at']
                )
            return None
        finally:
            conn.close()
    
    def get_admin_by_credentials(self, username: str, password: str) -> Optional[Admin]:
        """根据用户名和密码获取管理员"""
        conn = self.get_connection()
        try:
            row = conn.execute(
                'SELECT * FROM admins WHERE username = ? AND password = ?', 
                (username, password)
            ).fetchone()
            
            if row:
                return Admin(
                    admin_id=row['id'],
                    username=row['username'],
                    password=row['password'],
                    created_at=row['created_at']
                )
            return None
        finally:
            conn.close()
    
    def get_user_points(self, user_id: int) -> int:
        """获取用户积分"""
        conn = self.get_connection()
        try:
            row = conn.execute(
                'SELECT points FROM points WHERE user_id = ?', (user_id,)
            ).fetchone()
            return row['points'] if row else 0
        finally:
            conn.close()
    
    def create_consultation(self, user_id: int, budget_range: str, preferred_type: str,
                          usage_scenario: str, fuel_type: str, brand_preference: str,
                          consultation_content: str, llm_response: str) -> Tuple[bool, Optional[int], str]:
        """创建咨询记录"""
        conn = self.get_connection()
        try:
            cursor = conn.execute(
                '''INSERT INTO consultations 
                (user_id, budget_range, preferred_type, usage_scenario, fuel_type, brand_preference, consultation_content, llm_response) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)''',
                (user_id, budget_range, preferred_type, usage_scenario, fuel_type, brand_preference, consultation_content, llm_response)
            )
            consultation_id = cursor.lastrowid
            
            # 增加积分
            conn.execute(
                'UPDATE points SET points = points + 10, last_updated = CURRENT_TIMESTAMP WHERE user_id = ?',
                (user_id,)
            )
            conn.execute(
                'INSERT INTO point_records (user_id, points_change, reason) VALUES (?, ?, ?)',
                (user_id, 10, '完成购车咨询')
            )
            
            conn.commit()
            return True, consultation_id, "咨询记录创建成功"
            
        except Exception as e:
            return False, None, f"创建咨询记录失败: {str(e)}"
        finally:
            conn.close()
    
    def get_user_consultations(self, user_id: int) -> List[Consultation]:
        """获取用户的咨询记录"""
        conn = self.get_connection()
        try:
            rows = conn.execute(
                '''SELECT * FROM consultations 
                WHERE user_id = ? 
                ORDER BY created_at DESC''', 
                (user_id,)
            ).fetchall()
            
            consultations = []
            for row in rows:
                consultations.append(Consultation(
                    consultation_id=row['id'],
                    user_id=row['user_id'],
                    budget_range=row['budget_range'],
                    preferred_type=row['preferred_type'],
                    usage_scenario=row['usage_scenario'],
                    fuel_type=row['fuel_type'],
                    brand_preference=row['brand_preference'],
                    consultation_content=row['consultation_content'],
                    llm_response=row['llm_response'],
                    created_at=row['created_at']
                ))
            return consultations
        finally:
            conn.close()
    
    def get_consultation_by_id(self, consultation_id: int) -> Optional[Consultation]:
        """根据ID获取咨询记录"""
        conn = self.get_connection()
        try:
            row = conn.execute(
                'SELECT * FROM consultations WHERE id = ?', (consultation_id,)
            ).fetchone()
            
            if row:
                return Consultation(
                    consultation_id=row['id'],
                    user_id=row['user_id'],
                    budget_range=row['budget_range'],
                    preferred_type=row['preferred_type'],
                    usage_scenario=row['usage_scenario'],
                    fuel_type=row['fuel_type'],
                    brand_preference=row['brand_preference'],
                    consultation_content=row['consultation_content'],
                    llm_response=row['llm_response'],
                    created_at=row['created_at']
                )
            return None
        finally:
            conn.close()
    
    def get_all_consultations(self) -> List[Dict[str, Any]]:
        """获取所有咨询记录（管理员用）"""
        conn = self.get_connection()
        try:
            rows = conn.execute('''
                SELECT c.*, u.name as user_name, u.phone 
                FROM consultations c 
                JOIN users u ON c.user_id = u.id 
                ORDER BY c.created_at DESC
            ''').fetchall()
            
            consultations = []
            for row in rows:
                consultations.append({
                    'id': row['id'],
                    'user_name': row['user_name'],
                    'phone': row['phone'],
                    'budget_range': row['budget_range'],
                    'preferred_type': row['preferred_type'],
                    'usage_scenario': row['usage_scenario'],
                    'fuel_type': row['fuel_type'],
                    'brand_preference': row['brand_preference'],
                    'created_at': row['created_at']
                })
            return consultations
        finally:
            conn.close()
    
    def get_user_stats(self) -> UserStats:
        """获取用户统计信息"""
        conn = self.get_connection()
        try:
            # 总用户数
            total_users_row = conn.execute('SELECT COUNT(*) as count FROM users').fetchone()
            total_users = total_users_row['count'] if total_users_row else 0
            
            # 今日新增用户
            today = datetime.now().strftime('%Y-%m-%d')
            today_users_row = conn.execute(
                'SELECT COUNT(*) as count FROM users WHERE DATE(created_at) = ?', (today,)
            ).fetchone()
            today_users = today_users_row['count'] if today_users_row else 0
            
            # 咨询统计
            consultation_stats_row = conn.execute('''
                SELECT 
                    COUNT(*) as total_consultations,
                    COUNT(DISTINCT user_id) as active_users,
                    AVG(LENGTH(llm_response)) as avg_response_length
                FROM consultations
            ''').fetchone()
            
            total_consultations = consultation_stats_row['total_consultations'] if consultation_stats_row else 0
            active_users = consultation_stats_row['active_users'] if consultation_stats_row else 0
            avg_response_length = consultation_stats_row['avg_response_length'] if consultation_stats_row else 0
            
            # 热门车型
            popular_rows = conn.execute('''
                SELECT preferred_type, COUNT(*) as count 
                FROM consultations 
                GROUP BY preferred_type 
                ORDER BY count DESC 
                LIMIT 5
            ''').fetchall()
            
            popular_types = [{'type': row['preferred_type'], 'count': row['count']} for row in popular_rows]
            
            consultation_stats = ConsultationStats(
                total_consultations=total_consultations,
                active_users=active_users,
                avg_response_length=float(avg_response_length or 0),
                popular_types=popular_types
            )
            
            return UserStats(
                total_users=total_users,
                today_users=today_users,
                consultation_stats=consultation_stats
            )
            
        finally:
            conn.close()
    
    def add_points(self, user_id: int, points: int, reason: str) -> bool:
        """给用户添加积分"""
        conn = self.get_connection()
        try:
            # 更新总积分
            conn.execute(
                'UPDATE points SET points = points + ?, last_updated = CURRENT_TIMESTAMP WHERE user_id = ?',
                (points, user_id)
            )
            # 添加积分记录
            conn.execute(
                'INSERT INTO point_records (user_id, points_change, reason) VALUES (?, ?, ?)',
                (user_id, points, reason)
            )
            conn.commit()
            return True
        except Exception as e:
            print(f"添加积分失败: {e}")
            return False
        finally:
            conn.close()
    
    def get_point_records(self, user_id: int, limit: int = 10) -> List[PointRecord]:
        """获取用户的积分记录"""
        conn = self.get_connection()
        try:
            rows = conn.execute(
                'SELECT * FROM point_records WHERE user_id = ? ORDER BY created_at DESC LIMIT ?',
                (user_id, limit)
            ).fetchall()
            
            records = []
            for row in rows:
                records.append(PointRecord(
                    record_id=row['id'],
                    user_id=row['user_id'],
                    points_change=row['points_change'],
                    reason=row['reason'],
                    created_at=row['created_at']
                ))
            return records
        finally:
            conn.close()